{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Py3_Jupyter_Nb_迅捷集团_匹配相似门店(最小误差)_GF_2023-12-31.ipynb\n",
    "# Create By GF 2023-12-31 20:31"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import datetime\n",
    "# --------------------------------------------------\n",
    "import pandas\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def PdFunc_Calculate_Field_for_Past_N_Days_Average_Core(SrcPDF:pandas.core.frame.DataFrame, CalcFld:str, DstFld:str, N_Days:int):\n",
    "    \n",
    "    \"\"\"\n",
    "    [Require] import datetime\n",
    "    [Require] import pandas\n",
    "    \n",
    "    [Explain] PdFunc_Calculate_Field_for_Past_N_Days_Average_Core(\n",
    "                  SrcPDF:pandas.core.frame.DataFrame =\n",
    "                      pandas.DataFrame([{\"日期\":2023-03-01, \"门店名称\":\"云南丽江形象店\", \"毛利\":2831.4900},\n",
    "                                        {\"日期\":2023-03-02, \"门店名称\":\"云南丽江形象店\", \"毛利\":2292.8102},\n",
    "                                        {\"日期\":2023-03-03, \"门店名称\":\"云南丽江形象店\", \"毛利\":6139.3440},\n",
    "                                        {\"日期\":2023-03-04, \"门店名称\":\"云南丽江形象店\", \"毛利\":8607.4890},\n",
    "                                        {\"日期\":2023-03-05, \"门店名称\":\"云南丽江形象店\", \"毛利\":7918.3800}]),\n",
    "                  CalcFld:str = \"毛利\",\n",
    "                  DstFld:str = \"毛利(AVG60)\",\n",
    "                  N_Days:int = 60)\n",
    "    \n",
    "    [Example] +-----------+---------------+----------+    +-----------+---------------+----------+------------+\n",
    "              |日期       |门店名称       |毛利      |    |日期       |门店名称       |毛利      |毛利(AVG60) |\n",
    "              +-----------+---------------+----------+    +-----------+---------------+----------+------------+\n",
    "              |2023-03-01 |云南丽江形象店 |2831.4900 |    |2023-03-01 |云南丽江形象店 |2831.4900 |6467.9410   |\n",
    "              |2023-03-02 |云南丽江形象店 |2292.8102 | => |2023-03-02 |云南丽江形象店 |2292.8102 |6339.7180   |\n",
    "              |2023-03-03 |云南丽江形象店 |6139.3440 |    |2023-03-03 |云南丽江形象店 |6139.3440 |6318.7843   |\n",
    "              |2023-03-04 |云南丽江形象店 |8607.4890 |    |2023-03-04 |云南丽江形象店 |8607.4890 |6379.1235   |\n",
    "              |2023-03-05 |云南丽江形象店 |7918.3800 |    |2023-03-05 |云南丽江形象店 |7918.3800 |6455.5858   |\n",
    "              +-----------+---------------+----------+    +-----------+---------------+----------+------------+\n",
    "    \"\"\"\n",
    "    NewPDF = SrcPDF.copy()\n",
    "    NewPDF[\"日期\"] = NewPDF[\"日期\"].astype(\"datetime64[ns]\")\n",
    "        \n",
    "    MaxIdx = max(NewPDF.index)\n",
    "    # ----------------------------------------------\n",
    "    TimingBgn = datetime.datetime.now()\n",
    "    print(\"Start Time: %s\" % TimingBgn.strftime(\"%Y-%m-%d %H:%M:%S\"))\n",
    "\n",
    "    # Begin of Loop.\n",
    "    # ----------------------------------------------\n",
    "    for Idx in NewPDF.index:\n",
    "\n",
    "        EndDate = NewPDF.loc[Idx, \"日期\"]\n",
    "        BgnDate = EndDate - datetime.timedelta(days=N_Days)\n",
    "        # ..........................................\n",
    "        StoreName = NewPDF.loc[Idx, \"门店名称\"]\n",
    "        # ..........................................\n",
    "        DataForStore = NewPDF[NewPDF[\"门店名称\"] == StoreName]\n",
    "        DataForNdays = DataForStore[(BgnDate <= DataForStore[\"日期\"]) & (DataForStore[\"日期\"] <= EndDate)].copy()\n",
    "        \n",
    "        # Fill in Null Values (Optional).\n",
    "        # -> Average(1.0, 2.0, NaN) = (1.0 + 2.0) / 2 = 1.5\n",
    "        # -> Average(1.0, 2.0, 0.0) = (1.0 + 2.0 + 0.0) / 3 = 1.0\n",
    "        # -> If there are too many initial null values, it will cause the data to be low and distorted.\n",
    "        #DataForNdays[CalcFld] = DataForNdays[CalcFld].fillna(value=0.0)\n",
    "            \n",
    "        # Assignment:\n",
    "        # ..........................................\n",
    "        NewPDF.loc[Idx, DstFld] = round(DataForNdays[CalcFld].mean(), 4)\n",
    "            \n",
    "        # Schedule:\n",
    "        # ..........................................\n",
    "        print(\"%d / %d\" % (Idx, MaxIdx), end=\"\\r\", flush=True)\n",
    "    # ----------------------------------------------\n",
    "    # End of Loop.\n",
    "\n",
    "    TimingEnd = datetime.datetime.now()\n",
    "    print(\"Finish Time: %s\" % TimingEnd.strftime(\"%Y-%m-%d %H:%M:%S\"))\n",
    "    ElapseMinute = (TimingEnd - TimingBgn).seconds / 60\n",
    "    print(\"Time Consuming: %f Minute\" % ElapseMinute)\n",
    "    # ----------------------------------------------\n",
    "    NewPDF = NewPDF[[\"日期\", \"门店名称\", CalcFld, DstFld]]\n",
    "    # ##############################################\n",
    "    return NewPDF\n",
    "\n",
    "def PdFunc_Calculate_Field_for_Past_N_Days_Average(SrcPDF:pandas.core.frame.DataFrame, CalcFld:str, N_Days:int):\n",
    "    \n",
    "    \"\"\"\n",
    "    [Require] import os\n",
    "    [Require] import datetime\n",
    "    [Require] import pandas\n",
    "    \"\"\"\n",
    "    DstFld = \"%s(AVG%d)\" % (CalcFld, N_Days)\n",
    "    \n",
    "    # Check If The Data Cache Exists.\n",
    "    # ----------------------------------------------\n",
    "    if (\"XunJie_Data_Cache_Past_N_Days_Average.csv\" in os.listdir(\"./XunJie_Data_Cache\")):\n",
    "        \n",
    "        OldPDF = pd.read_csv(\"./XunJie_Data_Cache/XunJie_Data_Cache_Past_N_Days_Average.csv\", encoding=\"utf-8\")\n",
    "        OldPDF[\"日期\"] = OldPDF[\"日期\"].astype(\"datetime64[ns]\")\n",
    "        # ..........................................\n",
    "        if DstFld in OldPDF.columns:\n",
    "            return 0\n",
    "        else:\n",
    "            NewPDF = PdFunc_Calculate_Field_for_Past_N_Days_Average_Core(SrcPDF, CalcFld, DstFld, N_Days)\n",
    "            NewPDF = NewPDF[[\"日期\", \"门店名称\", DstFld]]\n",
    "            StoPDF = pd.merge(left=OldPDF, right=NewPDF, how=\"left\", on=[\"日期\", \"门店名称\"])\n",
    "            StoPDF.to_csv(\"./XunJie_Data_Cache/XunJie_Data_Cache_Past_N_Days_Average.csv\", encoding=\"utf-8\", index=False)\n",
    "    \n",
    "    # If The Data Cache Does Not Exist.\n",
    "    # ----------------------------------------------\n",
    "    else:\n",
    "        \n",
    "        NewPDF = PdFunc_Calculate_Field_for_Past_N_Days_Average_Core(SrcPDF, CalcFld, DstFld, N_Days)\n",
    "        NewPDF.to_csv(\"./XunJie_Data_Cache/XunJie_Data_Cache_Past_N_Days_Average.csv\", encoding=\"utf-8\", index=False)\n",
    "        return 0\n",
    "    # ##############################################\n",
    "    # End of Function.\n",
    "\n",
    "def PdFunc_Calculate_Field_for_SimilarRefer_By_Min_Error_Core(CacRefFld:str, DtmVal:datetime.datetime, DtmRng:int, RefVal:str):\n",
    "\n",
    "    \"\"\"\n",
    "    [Require] import datetime\n",
    "    [Require] import pandas\n",
    "    \n",
    "    [Explain] PdFunc_Calculate_Field_for_Similar_Refer_By_Min_Dif_Core(\n",
    "                  CacRefFld:str = \"门店名称\",\n",
    "                  DtmVal:datetime.datetime = datetime.datetime(2020, 11, 27, 0, 0, 0, 0),\n",
    "                  DtmRng:int = 60,\n",
    "                  RefVal:str = \"四川成都蜀都华为店\")\n",
    "    \n",
    "    [Example] +-----------+-----------------------+------------+-------------------+--------------+--------+\n",
    "              |日期       |门店名称               |毛利(AVG60) |R_门店名称         |R_毛利(AVG60) |Error   |\n",
    "              +-----------+-----------------------+------------+-------------------+--------------+--------+\n",
    "              |2020-11-27 |重庆荣昌人民路形象店   |5237.1719   |四川成都蜀都华为店 |5238.8134     |1.6145  |\n",
    "              |2020-11-27 |四川华阳正北中街形象店 |5224.8319   |四川成都蜀都华为店 |5238.8134     |13.9815 |\n",
    "              |2020-11-27 |云南玉溪形象店         |5149.1063   |四川成都蜀都华为店 |5238.8134     |89.7071 |\n",
    "              +-----------+-----------------------+------------+-------------------+--------------+--------+\n",
    "\n",
    "              Return: \"重庆荣昌人民路形象店\"\n",
    "    \"\"\"\n",
    "    DateBgnForFind = DtmVal\n",
    "    DateEndForFind = DateBgnForFind - datetime.timedelta(days=DtmRng)\n",
    "    \n",
    "    # Read Cache.\n",
    "    # ----------------------------------------------\n",
    "    CacPDF = pd.read_csv(\"./XunJie_Data_Cache/XunJie_Data_Cache_Past_N_Days_Average.csv\", encoding=\"utf-8\")\n",
    "    CacPDF[\"日期\"] = CacPDF[\"日期\"].astype(\"datetime64[ns]\")\n",
    "    \n",
    "    # Filter Left Table.\n",
    "    # ----------------------------------------------\n",
    "    LeftPDF = CacPDF[(DateEndForFind <= CacPDF[\"日期\"]) & (CacPDF[\"日期\"] <= DateBgnForFind)]\n",
    "    LeftPDF = LeftPDF[LeftPDF[CacRefFld] != RefVal]\n",
    "    \n",
    "    # Filter Right Table.\n",
    "    # ----------------------------------------------\n",
    "    RighPDF = CacPDF[(DateEndForFind <= CacPDF[\"日期\"]) & (CacPDF[\"日期\"] <= DateBgnForFind)]\n",
    "    RighPDF = RighPDF[RighPDF[CacRefFld] == RefVal]\n",
    "    RighPDF = RighPDF[[\"日期\", \"毛利(AVG60)\"]]\n",
    "    RighPDF = RighPDF.rename(columns={\"毛利(AVG60)\": \"R_毛利(AVG60)\"})\n",
    "    \n",
    "    # Connect Two Tables By Date.\n",
    "    # ----------------------------------------------\n",
    "    NewPDF = pd.merge(left=LeftPDF, right=RighPDF, how=\"left\", on=\"日期\")\n",
    "    \n",
    "    # Calculation Error.\n",
    "    # ----------------------------------------------\n",
    "    NewPDF[\"Error\"] = ((NewPDF[\"R_毛利(AVG60)\"] - NewPDF[\"毛利(AVG60)\"]) ** 2) ** 0.5\n",
    "    \n",
    "    # Aggregate Error.\n",
    "    # ----------------------------------------------\n",
    "    NewPDF = NewPDF[[CacRefFld, \"Error\"]].groupby(CacRefFld, as_index=False).mean(\"Error\")\n",
    "    NewPDF = NewPDF.sort_values(\"Error\", ascending=True).reset_index(drop=False)\n",
    "    \n",
    "    SimilarRefer = NewPDF.loc[0, CacRefFld]\n",
    "    # ##############################################\n",
    "    return SimilarRefer\n",
    "\n",
    "def PdFunc_Calculate_Field_for_SimilarRefer_By_Min_Error(DstPDF:pandas.core.frame.DataFrame, DstRefFld:str, DstDtmFld:str, DtmRng:int):\n",
    "\n",
    "    \"\"\"[Require] import pandas\"\"\"\n",
    "    NewPDF = DstPDF.copy()\n",
    "    \n",
    "    NewPDF[\"SimilarRefer\"] = None\n",
    "    \n",
    "    for Idx in NewPDF.index:\n",
    "        DstRefVal = NewPDF.loc[Idx, DstRefFld]\n",
    "        DstDtmVal = NewPDF.loc[Idx, DstDtmFld]\n",
    "        \n",
    "        SimilarReferVal = PdFunc_Calculate_Field_for_SimilarRefer_By_Min_Error_Core(DstRefFld, DstDtmVal, DtmRng, DstRefVal)\n",
    "        \n",
    "        NewPDF.loc[Idx, \"SimilarRefer\"] = SimilarReferVal\n",
    "        \n",
    "    return NewPDF\n",
    "\n",
    "def PdFunc_Calculate_Field_for_GrowthRate_in_Csv(DstCsv:str, RefFld:str, CalcFld:str):\n",
    "\n",
    "    \"\"\"\n",
    "    [Require] import pandas as pd\n",
    "    \n",
    "    [Explain] PdFunc_Calculate_Field_for_GrowthRate_in_Csv(\n",
    "                  DstCsv:str = \"./XunJie_Data_Cache/XunJie_Data_Cache_Past_N_Days_Average.csv\",\n",
    "                  RefFld:str = \"门店名称\",\n",
    "                  CalcFld:str = \"毛利(AVG60)\")\n",
    "    \n",
    "    [Example] Previous:\n",
    "              +-----------+-----------------------+----------+------------+\n",
    "              |日期       |门店名称               |毛利      |毛利(AVG60) |\n",
    "              +-----------+-----------------------+----------+------------+\n",
    "              |2021-09-03 |云南丽江形象店         |4418.792  |4418.7920   |\n",
    "              |2021-09-04 |云南丽江形象店         |2149.805  |3284.2985   |\n",
    "              |2021-09-05 |云南丽江形象店         |3684.275  |3417.6240   |\n",
    "              |2021-09-06 |云南丽江形象店         |1356.160  |2902.2580   |\n",
    "              |2023-12-13 |陕西西安高新万达形象店 |8462.559  |20772.0903  |\n",
    "              |2023-12-14 |陕西西安高新万达形象店 |9585.559  |21239.3561  |\n",
    "              |2023-12-15 |陕西西安高新万达形象店 |32495.474 |21447.4696  |\n",
    "              |2023-12-16 |陕西西安高新万达形象店 |44015.202 |21760.8907  |\n",
    "              +-----------+-----------------------+----------+------------+\n",
    "              \n",
    "              After:\n",
    "              +-----------+-----------------------+----------+------------+-----------+\n",
    "              |日期       |门店名称               |毛利      |毛利(AVG60) |GrowthRate |\n",
    "              +-----------+-----------------------+----------+------------+-----------+\n",
    "              |2021-09-03 |云南丽江形象店         |4418.792  |4418.7920   |NaN        |\n",
    "              |2021-09-04 |云南丽江形象店         |2149.805  |3284.2985   |-0.2567    |\n",
    "              |2021-09-05 |云南丽江形象店         |3684.275  |3417.6240   |0.0406     |\n",
    "              |2021-09-06 |云南丽江形象店         |1356.160  |2902.2580   |-0.1508    |\n",
    "              |2023-12-13 |陕西西安高新万达形象店 |8462.559  |20772.0903  |NaN        |\n",
    "              |2023-12-14 |陕西西安高新万达形象店 |9585.559  |21239.3561  |-0.0220    |\n",
    "              |2023-12-15 |陕西西安高新万达形象店 |32495.474 |21447.4696  |0.0098     |\n",
    "              |2023-12-16 |陕西西安高新万达形象店 |44015.202 |21760.8907  |0.0146     |\n",
    "              +-----------+-----------------------+----------+------------+-----------+\n",
    "    \"\"\"\n",
    "    OldPDF = pd.read_csv(DstCsv, encoding=\"utf-8\")\n",
    "    NewPDF = OldPDF.copy()\n",
    "    \n",
    "    NewPDF = NewPDF[NewPDF[CalcFld].isna() == False]\n",
    "    \n",
    "    NewPDF = NewPDF.sort_values([RefFld, \"日期\"], ascending=True)\n",
    "    \n",
    "    NewPDF[\"GrowthRate\"] = None\n",
    "    \n",
    "    ShiftRefFld  = \"%s_Shift\" % RefFld\n",
    "    ShiftCalcFld = \"%s_Shift\" % CalcFld\n",
    "    \n",
    "    NewPDF[\"日期_Shift\"] = NewPDF[\"日期\"].shift(1)\n",
    "    NewPDF[ShiftRefFld]  = NewPDF[RefFld].shift(1)\n",
    "    NewPDF[ShiftCalcFld] = NewPDF[CalcFld].shift(1)\n",
    "    \n",
    "    for Idx in NewPDF.index:\n",
    "        SrcDtmVal = NewPDF.loc[Idx, \"日期\"]\n",
    "        SrcRefVal = NewPDF.loc[Idx, RefFld]\n",
    "        ShiftDtmVal = NewPDF.loc[Idx, \"日期_Shift\"]\n",
    "        ShiftRefVal = NewPDF.loc[Idx, ShiftRefFld]\n",
    "        \n",
    "        if (SrcDtmVal != ShiftDtmVal) and (SrcRefVal != ShiftRefVal):\n",
    "            NewPDF.loc[Idx, \"日期_Shift\"] = None\n",
    "            NewPDF.loc[Idx, ShiftRefFld] = None\n",
    "            NewPDF.loc[Idx, ShiftCalcFld] = None\n",
    "            continue\n",
    "        \n",
    "        SrcCalcVal   = NewPDF.loc[Idx, CalcFld]\n",
    "        ShiftCalcVal = NewPDF.loc[Idx, ShiftCalcFld]\n",
    "        NewPDF.loc[Idx, \"GrowthRate\"] = round((SrcCalcVal - ShiftCalcVal) / ShiftCalcVal, 4)\n",
    "    \n",
    "    NewPDF = NewPDF[[\"日期\", RefFld, \"GrowthRate\"]]\n",
    "    NewPDF = pd.merge(left=OldPDF, right=NewPDF, how=\"left\", on=[\"日期\", RefFld])\n",
    "    NewPDF.to_csv(DstCsv, encoding=\"utf-8\", index=False)\n",
    "    # ##############################################\n",
    "    return 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "PDF_Cache = pd.read_csv(\"./XunJie_Data_Cache/XunJie_Data_Cache_Past_N_Days_Average.csv\", encoding=\"utf-8\")\n",
    "PDF_Cache[\"日期\"] = PDF_Cache[\"日期\"].astype(\"datetime64[ns]\")\n",
    "# --------------------------------------------------\n",
    "PDF_StoreProfit = pd.read_csv(\"./Datas/CSV数据_讯捷集团_毛利数据_门店毛利数据_2020-01-01至2023-12-31.csv\", encoding=\"utf-8\")\n",
    "PDF_StoreProfit = PDF_StoreProfit.rename(columns={\"xj_date\"             : \"日期\",\n",
    "                                                  \"xj_year_month\"       : \"年月\",\n",
    "                                                  \"xj_year\"             : \"年\",\n",
    "                                                  \"xj_month\"            : \"月\",\n",
    "                                                  \"xj_year_half\"        : \"半年\",\n",
    "                                                  \"xj_week_sn\"          : \"周序列\",\n",
    "                                                  \"xj_week_what_day\"    : \"周内第几天\",\n",
    "                                                  \"xj_date_level\"       : \"日期等级\",\n",
    "                                                  \"xj_day_level_name\"   : \"日等级名称\",\n",
    "                                                  \"xj_ent_system\"       : \"企业体系\",\n",
    "                                                  \"xj_ent_depart\"       : \"企业部门\",\n",
    "                                                  \"xj_ent_bo\"           : \"企业分公司\",\n",
    "                                                  \"xj_ent_center\"       : \"企业中心\",\n",
    "                                                  \"xj_sto_id\"           : \"门店ID\",\n",
    "                                                  \"xj_sto_name\"         : \"门店名称\",\n",
    "                                                  \"xj_sto_level\"        : \"门店等级\",\n",
    "                                                  \"xj_sto_status\"       : \"门店状态\",\n",
    "                                                  \"xj_sto_open\"         : \"门店开店日期\",\n",
    "                                                  \"xj_sto_close\"        : \"门店闭店日期\",\n",
    "                                                  \"xj_sto_close_deco\"   : \"门店闭店装修\",\n",
    "                                                  \"xj_sto_cls_dec_days\" : \"门店闭店装修天数\",\n",
    "                                                  \"xj_region_class\"     : \"区域类别\",\n",
    "                                                  \"xj_region_biz_id\"    : \"区域商圈ID\",\n",
    "                                                  \"xj_region_biz\"       : \"区域商圈\",\n",
    "                                                  \"xj_gr_p\"             : \"毛利\",\n",
    "                                                  \"xj_gr_p_md_avg\"      : \"毛利月日均\",\n",
    "                                                  \"xj_gr_p_yd_avg\"      : \"毛利年日均\",\n",
    "                                                  \"xj_gr_p_m_sum\"       : \"毛利月合计\",\n",
    "                                                  \"xj_gr_p_y_sum\"       : \"毛利年合计\"})\n",
    "PDF_StoreProfit[\"日期\"] = PDF_StoreProfit[\"日期\"].astype(\"datetime64[ns]\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Start Time: 2024-01-02 15:35:13\n",
      "Finish Time: 2024-01-02 18:24:21\n",
      "Time Consuming: 169.116667 Minute\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pandas Processe: 计算每个门店的过去60日平均毛利.\n",
    "# --------------------------------------------------\n",
    "PdFunc_Calculate_Field_for_Past_N_Days_Average(PDF_StoreProfit, \"毛利\", 30)\n",
    "PdFunc_Calculate_Field_for_Past_N_Days_Average(PDF_StoreProfit, \"毛利\", 60)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pandas Processe: 计算每个门店的平均毛利增长率.\n",
    "# --------------------------------------------------\n",
    "PdFunc_Calculate_Field_for_GrowthRate_in_Csv(DstCsv=\"./XunJie_Data_Cache/XunJie_Data_Cache_Past_N_Days_Average.csv\",\n",
    "                                             RefFld=\"门店名称\",\n",
    "                                             CalcFld=\"毛利(AVG60)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>门店名称</th>\n",
       "      <th>毛利</th>\n",
       "      <th>毛利(AVG60)</th>\n",
       "      <th>SimilarRefer</th>\n",
       "      <th>SimilarProfit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>134092</th>\n",
       "      <td>2022-06-05</td>\n",
       "      <td>四川双流东升华为店</td>\n",
       "      <td>5824.3250</td>\n",
       "      <td>8302.0340</td>\n",
       "      <td>四川崇州广场形象店</td>\n",
       "      <td>8896.1736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14001</th>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>云南大理泰安路155号形象店</td>\n",
       "      <td>619.0000</td>\n",
       "      <td>6264.6384</td>\n",
       "      <td>贵州兴义形象店</td>\n",
       "      <td>5822.992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143295</th>\n",
       "      <td>2022-05-11</td>\n",
       "      <td>重庆沙坪坝华宇形象店</td>\n",
       "      <td>1738.8942</td>\n",
       "      <td>6162.2953</td>\n",
       "      <td>云南弥勒冉翁东路形象店</td>\n",
       "      <td>5649.5927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>271590</th>\n",
       "      <td>2021-01-04</td>\n",
       "      <td>四川南充五星形象店</td>\n",
       "      <td>11700.4820</td>\n",
       "      <td>10284.4165</td>\n",
       "      <td>陕西西安城市立方形象店</td>\n",
       "      <td>10472.3844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205198</th>\n",
       "      <td>2021-10-31</td>\n",
       "      <td>陕西咸阳兴平形象店</td>\n",
       "      <td>2742.8100</td>\n",
       "      <td>3037.5334</td>\n",
       "      <td>重庆江津步行街形象店</td>\n",
       "      <td>2056.2318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214353</th>\n",
       "      <td>2021-08-04</td>\n",
       "      <td>重庆荣昌人民路形象店</td>\n",
       "      <td>5464.0300</td>\n",
       "      <td>5232.6675</td>\n",
       "      <td>四川邛崃东街形象店</td>\n",
       "      <td>4973.8338</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186714</th>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>贵州六盘水钟山大道形象店</td>\n",
       "      <td>21479.7920</td>\n",
       "      <td>10618.2110</td>\n",
       "      <td>云南昆明环城东路形象店</td>\n",
       "      <td>9718.5972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>345369</th>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>四川都江堰华为plus店</td>\n",
       "      <td>12100.3294</td>\n",
       "      <td>9418.1039</td>\n",
       "      <td>重庆北碚胜利路形象店</td>\n",
       "      <td>9697.4365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130569</th>\n",
       "      <td>2022-07-23</td>\n",
       "      <td>重庆两路形象店</td>\n",
       "      <td>30527.1997</td>\n",
       "      <td>8380.0415</td>\n",
       "      <td>贵州遵义丁字口华为店</td>\n",
       "      <td>8380.2907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>365558</th>\n",
       "      <td>2020-02-15</td>\n",
       "      <td>云南曲靖形象店</td>\n",
       "      <td>8957.9677</td>\n",
       "      <td>9285.4838</td>\n",
       "      <td>重庆荣昌人民路形象店</td>\n",
       "      <td>8578.0509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251834</th>\n",
       "      <td>2021-04-20</td>\n",
       "      <td>四川绵阳红宝石形象店</td>\n",
       "      <td>12077.3960</td>\n",
       "      <td>18035.6753</td>\n",
       "      <td>四川双流淳化街形象店</td>\n",
       "      <td>20458.9088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280519</th>\n",
       "      <td>2020-12-06</td>\n",
       "      <td>四川绵阳红宝石形象店</td>\n",
       "      <td>14327.1099</td>\n",
       "      <td>16582.1293</td>\n",
       "      <td>贵州贵阳喷水池北门形象店</td>\n",
       "      <td>17512.5195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88638</th>\n",
       "      <td>2022-12-10</td>\n",
       "      <td>四川郫县犀浦形象二店</td>\n",
       "      <td>10201.3400</td>\n",
       "      <td>12609.8280</td>\n",
       "      <td>四川新津图腾街形象店</td>\n",
       "      <td>12873.6715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13475</th>\n",
       "      <td>2023-11-28</td>\n",
       "      <td>陕西榆林新建南路形象店</td>\n",
       "      <td>11595.4520</td>\n",
       "      <td>12195.5147</td>\n",
       "      <td>云南保山形象店</td>\n",
       "      <td>12137.8246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31911</th>\n",
       "      <td>2023-08-16</td>\n",
       "      <td>四川青白江青江北路华为店</td>\n",
       "      <td>16801.8497</td>\n",
       "      <td>7529.5662</td>\n",
       "      <td>重庆沙坪坝华宇形象店</td>\n",
       "      <td>7350.365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141044</th>\n",
       "      <td>2022-05-02</td>\n",
       "      <td>四川都江堰财经大厦形象店</td>\n",
       "      <td>9543.2480</td>\n",
       "      <td>6448.7205</td>\n",
       "      <td>贵州贵阳虹祥华为店</td>\n",
       "      <td>5898.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>332061</th>\n",
       "      <td>2020-06-13</td>\n",
       "      <td>贵州贵阳白云同心路华为店</td>\n",
       "      <td>10290.5800</td>\n",
       "      <td>10843.2890</td>\n",
       "      <td>贵州贵阳金阳形象店</td>\n",
       "      <td>10734.6881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205525</th>\n",
       "      <td>2021-09-01</td>\n",
       "      <td>重庆两路形象店</td>\n",
       "      <td>7807.1900</td>\n",
       "      <td>6462.1053</td>\n",
       "      <td>云南曲靖飞龙形象店</td>\n",
       "      <td>5774.2806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>346827</th>\n",
       "      <td>2020-04-07</td>\n",
       "      <td>四川龙泉通达形象店</td>\n",
       "      <td>2442.8400</td>\n",
       "      <td>9542.1132</td>\n",
       "      <td>贵州贵阳黄河形象店</td>\n",
       "      <td>9755.4209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123684</th>\n",
       "      <td>2022-08-27</td>\n",
       "      <td>重庆两路形象店</td>\n",
       "      <td>8386.9300</td>\n",
       "      <td>7934.6196</td>\n",
       "      <td>四川温江大南街华为店</td>\n",
       "      <td>8579.4299</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               日期            门店名称          毛利   毛利(AVG60)  SimilarRefer  \\\n",
       "134092 2022-06-05       四川双流东升华为店   5824.3250   8302.0340     四川崇州广场形象店   \n",
       "14001  2023-11-30  云南大理泰安路155号形象店    619.0000   6264.6384       贵州兴义形象店   \n",
       "143295 2022-05-11      重庆沙坪坝华宇形象店   1738.8942   6162.2953   云南弥勒冉翁东路形象店   \n",
       "271590 2021-01-04       四川南充五星形象店  11700.4820  10284.4165   陕西西安城市立方形象店   \n",
       "205198 2021-10-31       陕西咸阳兴平形象店   2742.8100   3037.5334    重庆江津步行街形象店   \n",
       "214353 2021-08-04      重庆荣昌人民路形象店   5464.0300   5232.6675     四川邛崃东街形象店   \n",
       "186714 2021-12-24    贵州六盘水钟山大道形象店  21479.7920  10618.2110   云南昆明环城东路形象店   \n",
       "345369 2020-04-01    四川都江堰华为plus店  12100.3294   9418.1039    重庆北碚胜利路形象店   \n",
       "130569 2022-07-23         重庆两路形象店  30527.1997   8380.0415    贵州遵义丁字口华为店   \n",
       "365558 2020-02-15         云南曲靖形象店   8957.9677   9285.4838    重庆荣昌人民路形象店   \n",
       "251834 2021-04-20      四川绵阳红宝石形象店  12077.3960  18035.6753    四川双流淳化街形象店   \n",
       "280519 2020-12-06      四川绵阳红宝石形象店  14327.1099  16582.1293  贵州贵阳喷水池北门形象店   \n",
       "88638  2022-12-10      四川郫县犀浦形象二店  10201.3400  12609.8280    四川新津图腾街形象店   \n",
       "13475  2023-11-28     陕西榆林新建南路形象店  11595.4520  12195.5147       云南保山形象店   \n",
       "31911  2023-08-16    四川青白江青江北路华为店  16801.8497   7529.5662    重庆沙坪坝华宇形象店   \n",
       "141044 2022-05-02    四川都江堰财经大厦形象店   9543.2480   6448.7205     贵州贵阳虹祥华为店   \n",
       "332061 2020-06-13    贵州贵阳白云同心路华为店  10290.5800  10843.2890     贵州贵阳金阳形象店   \n",
       "205525 2021-09-01         重庆两路形象店   7807.1900   6462.1053     云南曲靖飞龙形象店   \n",
       "346827 2020-04-07       四川龙泉通达形象店   2442.8400   9542.1132     贵州贵阳黄河形象店   \n",
       "123684 2022-08-27         重庆两路形象店   8386.9300   7934.6196    四川温江大南街华为店   \n",
       "\n",
       "       SimilarProfit  \n",
       "134092     8896.1736  \n",
       "14001       5822.992  \n",
       "143295     5649.5927  \n",
       "271590    10472.3844  \n",
       "205198     2056.2318  \n",
       "214353     4973.8338  \n",
       "186714     9718.5972  \n",
       "345369     9697.4365  \n",
       "130569     8380.2907  \n",
       "365558     8578.0509  \n",
       "251834    20458.9088  \n",
       "280519    17512.5195  \n",
       "88638     12873.6715  \n",
       "13475     12137.8246  \n",
       "31911       7350.365  \n",
       "141044       5898.08  \n",
       "332061    10734.6881  \n",
       "205525     5774.2806  \n",
       "346827     9755.4209  \n",
       "123684     8579.4299  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pandas Examples: 查找相似门店.\n",
    "# --------------------------------------------------\n",
    "PDF_Test = PDF_Cache[PDF_Cache[\"毛利\"].isna() == False]\n",
    "PDF_Test = PDF_Test.sample(20)\n",
    "\n",
    "# Calling Function.\n",
    "# --------------------------------------------------\n",
    "PDF_Test = PdFunc_Calculate_Field_for_SimilarRefer_By_Min_Error(DstPDF=PDF_Test, DstRefFld=\"门店名称\", DstDtmFld=\"日期\", DtmRng=60)\n",
    "\n",
    "PDF_Test[\"SimilarProfit\"] = None\n",
    "\n",
    "# Loop Begin.\n",
    "# --------------------------------------------------\n",
    "for Idx in PDF_Test.index:\n",
    "    SimRef  = PDF_Test.loc[Idx, \"SimilarRefer\"]\n",
    "    SimDate = PDF_Test.loc[Idx, \"日期\"]\n",
    "    # ..............................................\n",
    "    FilterPDF = PDF_Cache[(PDF_Cache[\"日期\"] == SimDate) & (PDF_Cache[\"门店名称\"] == SimRef)]\n",
    "    if FilterPDF[\"毛利(AVG60)\"].values.size > 0:\n",
    "        PDF_Test.loc[Idx, \"SimilarProfit\"] = FilterPDF[\"毛利(AVG60)\"].values[0]\n",
    "# --------------------------------------------------\n",
    "# Loop End.\n",
    "\n",
    "PDF_Test[[\"日期\", \"门店名称\", \"毛利\", \"毛利(AVG60)\", \"SimilarRefer\", \"SimilarProfit\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# EOF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
